package com.gxa.utils.batch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Random;

/**
 * a class for adding data to the table,modify as needed
 * @author IKE <hp1041735092@outlook.jp>
 * @date 25/2/2024
 */
public class SqlScripts {
    @SuppressWarnings("all")
    public static void main(String[] args) {
        // link the database
        String url = "jdbc:mysql://rm-cn-jte3mfrg800015uo.rwlb.cn-chengdu.rds.aliyuncs.com/zhyl";
        String username = "user_root";
        String password = "Hello_World";

        // SQL insert
        String sql = "INSERT INTO t_visit (interviewed_oldman, date, visit, " + "visit_tel, " +
                "visit_cardtype, visit_cardnum, visit_relation, visit_num, " + "visitat_time, " +
                "visit_status, comment) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        // number of data to insert
        int numTestData = 3000;

        try (
                // build connection
                Connection connection = DriverManager.getConnection(url, username, password);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ) {
            // loop insert
            for (int i = 0; i < numTestData; i++) {
                // Random generate
                String interviewedOldman = "Oldman_" + i;
                LocalDate date = LocalDate.now().minusDays(new Random().nextInt(365));
                String visit = "Visit_" + i;
                String visitTel = String.valueOf(new Random().nextInt(1000000000));
                String visitCardType = "CardType_" + i;
                String visitCardNum = "CardNum_" + i;
                String visitRelation = "Relation_" + i;
                int visitNum = new Random().nextInt(10);

                // For visitAtTime, ensure it's within the same date range as 'date'
                LocalDate visitAtDate = date;
                LocalTime randomTime = LocalTime.of(
                        new Random().nextInt(23) + 1, // Hours from 1 to 23
                        new Random().nextInt(60), // Minutes from 0 to 59
                        0); // Seconds fixed at 0
                LocalDateTime visitAtTime = LocalDateTime.of(visitAtDate, randomTime);

                String visitStatus = "Status_" + i;
                String comment = "Comment_" + i;

                // params
                preparedStatement.setString(1, interviewedOldman);
                preparedStatement.setObject(2, date);
                preparedStatement.setString(3, visit);
                preparedStatement.setString(4, visitTel);
                preparedStatement.setString(5, visitCardType);
                preparedStatement.setString(6, visitCardNum);
                preparedStatement.setString(7, visitRelation);
                preparedStatement.setInt(8, visitNum);
                preparedStatement.setObject(9, visitAtTime);
                preparedStatement.setString(10, visitStatus);
                preparedStatement.setString(11, comment);

                // execute
                preparedStatement.executeUpdate();
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
